{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "e873bdaa",
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    },
    "ExecuteTime": {
     "end_time": "2023-10-14T06:31:46.118260500Z",
     "start_time": "2023-10-14T06:31:46.073189Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import spacy\n",
    "import re\n",
    "import openpyxl\n",
    "from warnings import filterwarnings\n",
    "from collections import Counter\n",
    "from pandas import DataFrame\n",
    "\n",
    "filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "outputs": [],
   "source": [
    "special_char = set()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-14T06:31:46.141260600Z",
     "start_time": "2023-10-14T06:31:46.091242100Z"
    }
   },
   "id": "bb5d6f49a1218136"
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "f217ca20f3b1dd44",
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    },
    "ExecuteTime": {
     "end_time": "2023-10-14T06:31:46.142261300Z",
     "start_time": "2023-10-14T06:31:46.105249700Z"
    }
   },
   "outputs": [],
   "source": [
    "class EnDataPreprocessing:\n",
    "    \"\"\"\n",
    "    一个英文数据清洗的 工具类\n",
    "    \"\"\"\n",
    "\n",
    "    def __init__(self, data_frame):\n",
    "        \"\"\"\n",
    "        初始化 工具类\n",
    "        :param data_frame: 传入Dataframe对象\n",
    "        \"\"\"\n",
    "        df.dropna(inplace=True)\n",
    "        self.df = data_frame\n",
    "\n",
    "    def convert_fullwidth_to_halfWidth(self, text):\n",
    "        '''\n",
    "        全角字符统一为半全角字符\n",
    "        '''\n",
    "        halfwidth_chars = \"1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ!\\\"#$%&'()*+,-./:;<=>?@[\\\\]^_`{|}~\"\n",
    "\n",
    "        # 对应的全角字符Unicode范围\n",
    "        fullwidth_chars = \"１２３４５６７８９０ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ！“＃＄％＆'（）＊＋，－．／：；＜＝＞？＠［＼］＾＿｀｛｜｝～\"\n",
    "\n",
    "        char_mapping = dict()\n",
    "        for i in range(len(halfwidth_chars)):\n",
    "            char_mapping[ord(fullwidth_chars[i])] = halfwidth_chars[i]\n",
    "        return text.translate(char_mapping)\n",
    "\n",
    "    def chinese_punctuation_to_english(self, text):\n",
    "        '''\n",
    "        中文标点符号统一为英文\n",
    "        '''\n",
    "        punctuation_mapping = {'，': ',', '。': '.', '？': '?', '！': '!', '；': ';', '：': ':', '“': '\"', '”': '\"', '‘': \"'\",\n",
    "                               '’': \"'\", '&#8203;``oaicite:{\"number\":1,\"invalid_reason\":\"Malformed citation 【': '[',\n",
    "                               '】\"}``&#8203;': ']', '（': '(', '）': ')', '《': '<', '》': '>', '、': ',', '……': '...',\n",
    "                               '·': '.',\n",
    "                               '——': '-'}\n",
    "        # 使用正则表达式替换中文标点符号\n",
    "        for ch, en in punctuation_mapping.items():\n",
    "            text = re.sub(re.escape(ch), en, text)\n",
    "        return text\n",
    "\n",
    "    def Abbreviation_replacement(self, text):\n",
    "        # 缩写和它们的扩展 字典\n",
    "        abbreviations = {\"can't\": 'cannot', \"it's\": 'it is', \"I'm\": 'I am', 'gonna': 'going to', 'wanna': 'want to',\n",
    "                         \"shouldn't\": 'should not', \"didn't\": 'did not', \"couldn't\": 'could not', \"doesn't\": 'does not',\n",
    "                         \"won't\": 'will not', \"i'll\": 'I will', \"you'll\": 'you will', \"he'll\": 'he will',\n",
    "                         \"she'll\": 'she will', \"we'll\": 'we will', \"they'll\": 'they will', \"I've\": 'I have',\n",
    "                         \"you've\": 'you have', \"we've\": 'we have', \"they've\": 'they have', \"I'd\": 'I would',\n",
    "                         \"you'd\": 'you would', \"he'd\": 'he would', \"she'd\": 'she would', \"we'd\": 'we would',\n",
    "                         \"they'd\": 'they would', \"haven't\": 'have not', \"hasn't\": 'has not', \"wouldn't\": 'would not',\n",
    "                         \"should've\": 'should have', \"could've\": 'could have', \"might've\": 'might have',\n",
    "                         \"must've\": 'must have', \"Can't\": 'Cannot', \"It's\": 'It is', 'Gonna': 'Going to',\n",
    "                         'Wanna': 'Want to', \"Shouldn't\": 'Should not', \"Didn't\": 'Did not', \"Couldn't\": 'Could not',\n",
    "                         \"Doesn't\": 'Does not', \"Won't\": 'Will not', \"I'll\": 'I will', \"You'll\": 'You will',\n",
    "                         \"He'll\": 'He will', \"She'll\": 'She will', \"We'll\": 'We will', \"They'll\": 'They will',\n",
    "                         \"You've\": 'You have', \"We've\": 'We have', \"They've\": 'They have', \"You'd\": 'You would',\n",
    "                         \"He'd\": 'He would', \"She'd\": 'She would', \"We'd\": 'We would', \"They'd\": 'They would',\n",
    "                         \"Haven't\": 'Have not', \"Hasn't\": 'Has not', \"Wouldn't\": 'Would not',\n",
    "                         \"Should've\": 'Should have',\n",
    "                         \"Could've\": 'Could have', \"Might've\": 'Might have', \"Must've\": 'Must have'}\n",
    "\n",
    "        # 替换缩写\n",
    "        for abbreviation, expansion in abbreviations.items():\n",
    "            # 使用正则表达式确保只替换完整的单词，而不是部分匹配\n",
    "            text = re.sub(r'\\b' + re.escape(abbreviation) + r'\\b', expansion, text)\n",
    "        return text\n",
    "\n",
    "    def replace_special_char(self, cleaned_text):\n",
    "        \"\"\"\n",
    "        去除特殊字符\n",
    "        :param cleaned_text: \n",
    "        :return: \n",
    "        \"\"\"\n",
    "        for i in ['*', \"@\", \"#\"]:\n",
    "            cleaned_text = cleaned_text.replace(i, ' ')\n",
    "\n",
    "        cleaned_text = cleaned_text.replace(\"&\", \" and \")\n",
    "        cleaned_text = cleaned_text.replace(\"|\", \" or \")\n",
    "\n",
    "        for i in re.findall(\"\\$\\d+,\\d+\", cleaned_text):\n",
    "            cleaned_text = cleaned_text.replace(i, re.sub(\"\\S|,\", \"\", i))\n",
    "\n",
    "        for i in ['<', '=', '>', '[', '\\\\', ']', '{', '}', \"%\",\"+\",\"=\"]:\n",
    "            cleaned_text = cleaned_text.replace(i, '')\n",
    "\n",
    "        # cleaned_text = re.sub(r\"\\n+\", \" \", cleaned_text, re.S)\n",
    "        # doc = self.nlp(cleaned_text)\n",
    "        # sentences = list(doc.sents)\n",
    "        # cleaned_text = \"\"\n",
    "        # for sentence in sentences:\n",
    "        #     sentence = sentence.text\n",
    "        #     if re.findall(r'\\n+', sentence, re.S):\n",
    "        #         for x in re.findall(r\"[a-z]\\W\\n+[A-Z]\",sentence,re.S):\n",
    "        #             sentence = sentence.replace(x, re.sub(r\"\\n+\",\" \",x,re.S))\n",
    "        #         for x in re.findall(r\"[a-z]\\n+[A-Z]\",sentence,re.S):\n",
    "        #             sentence = sentence.replace(x, re.sub(r\"\\n+\",\". \",x,re.S))\n",
    "        #     cleaned_text += sentence.strip()\n",
    "        #     # cleaned_text += sentence\n",
    "        # print(cleaned_text)\n",
    "        # print(\"***\"*10)\n",
    "        # doc = self.nlp(cleaned_text)\n",
    "        # # 词干化\n",
    "        # cleaned_text = \" \".join([token.text for token in doc])\n",
    "\n",
    "        # # 匹配各种英文序号的正则表达式\n",
    "        # pattern = r'\\b(?:[A-Z]?[a-z]*\\)?|i{1,3}|IV|V?I{1,3})\\.\\s*'\n",
    "        # # 使用正则表达式替换为空字符串\n",
    "        # cleaned_text = re.sub(pattern, '', cleaned_text)\n",
    "\n",
    "        # 去除URLs\n",
    "        cleaned_text = re.sub(r'http\\S+|www\\S+|https\\S+', '', cleaned_text)\n",
    "\n",
    "        # 去除HTML标记\n",
    "        pattern = re.compile('<.*?>')\n",
    "        cleaned_text = re.sub(pattern, '', cleaned_text)\n",
    "\n",
    "        # # 使用 spacy 去停顿词\n",
    "        # doc = self.nlp(cleaned_text)\n",
    "        # cleaned_text = [token.text for token in doc if not token.is_stop]\n",
    "        # \n",
    "        # # 使用自己的停顿词list 去除停用词\n",
    "        # cleaned_text = \" \".join([word for word in cleaned_text if word not in stopList and len(word) > 3])\n",
    "\n",
    "        # 去除特殊字符\n",
    "        # special_char_pattern = re.compile('[^\\w\\s]')\n",
    "        # cleaned_text = re.sub(special_char_pattern, ' ', cleaned_text)\n",
    "\n",
    "        # 去掉多余的空格\n",
    "        cleaned_text = \" \".join([char.strip() for char in cleaned_text.split()])\n",
    "        # 去除非ASCII字符\n",
    "        cleaned_text = ''.join([char for char in cleaned_text if ord(char) < 128])\n",
    "\n",
    "        special_char.update(set(re.findall(\"\\W\", cleaned_text)))\n",
    "        return cleaned_text\n",
    "\n",
    "    def run(self, text):\n",
    "        \"\"\"\n",
    "        文本清洗\n",
    "        :param text: \n",
    "        \"\"\"\n",
    "        # 中英文 标点符号统一  中文符号转换为英文\n",
    "        text = self.chinese_punctuation_to_english(text)\n",
    "\n",
    "        # 全角字符统一为半全角字符\n",
    "        text = self.convert_fullwidth_to_halfWidth(text.strip())\n",
    "        # 替换缩写\n",
    "        text = self.Abbreviation_replacement(text)\n",
    "\n",
    "        # 去除特殊字符\n",
    "        text = self.replace_special_char(text)\n",
    "        return text\n",
    "\n",
    "    def data_preprocessing(self, row):\n",
    "        \"\"\"\n",
    "        清洗文本的工具函数\n",
    "        :param row: self.df 的每一行 \n",
    "        :return: 返回处理后的 self.df每一行 \n",
    "        \"\"\"\n",
    "        row['任职要求'] = self.run(row['任职要求'])\n",
    "        row['职位'] = self.run(row['职位'])\n",
    "        return row\n",
    "\n",
    "    def main_(self) -> DataFrame:\n",
    "        \"\"\"\n",
    "        执行函数\n",
    "        :return: 文本清洗后的 df \n",
    "        \"\"\"\n",
    "        self.df.apply(func=self.data_preprocessing, axis=1)\n",
    "        return self.df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "d60c0177",
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-14T06:31:49.789690600Z",
     "start_time": "2023-10-14T06:31:46.122260900Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "当前网站: CareerBuilder\n",
      "已保存 CareerBuilder.csv\n"
     ]
    }
   ],
   "source": [
    "workbook = openpyxl.load_workbook('../Data/数据汇总.xlsx')\n",
    "# lac = hub.Module(directory='D:\\paddlenlp_home\\paddle_hub\\modules\\lac')\n",
    "stopList = open(\"../Data/stop_words.txt\", 'r', encoding='utf-8').read().split('\\n')\n",
    "\n",
    "for sheet_name in workbook.sheetnames:\n",
    "    print(f\"当前网站: {sheet_name}\")\n",
    "    df = pd.read_excel('../Data/数据汇总.xlsx', sheet_name=sheet_name, index_col=0)\n",
    "    if re.search('[\\u4e00-\\u9fa5]', sheet_name):  # 中文网站\n",
    "        # ZhWordMark(lac,sheet_name,df,stopList).run()\n",
    "        # print(\"*-\" * 80)\n",
    "        pass\n",
    "    else:  # 英文网站\n",
    "        df = EnDataPreprocessing(df).main_()\n",
    "        df.to_csv(f'{sheet_name}.csv')\n",
    "        print(f'已保存 {sheet_name}.csv')\n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "b5b0baaca32b7171",
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-14T06:31:49.846784500Z",
     "start_time": "2023-10-14T06:31:49.787691500Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "{' ', '!', '\"', '$', \"'\", '(', ')', ',', '-', '.', '/', ':', ';', '?', '~'}"
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "special_char\n",
    "# data = df['任职要求'].str.split().explode().tolist()\n",
    "# count = Counter(data).most_common()\n",
    "# count"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
